package com.repair.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.repair.entity.RepairOrder;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Param;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Mapper
public interface RepairOrderMapper extends BaseMapper<RepairOrder> {
    
    @Select("SELECT DATE(create_time) as date, COUNT(*) as count " +
            "FROM repair_order " +
            "WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) " +
            "GROUP BY DATE(create_time) " +
            "ORDER BY date")
    List<Map<String, Object>> selectOrderStatistics();
    
    @Select("SELECT r.username as repairmanName, COUNT(*) as orderCount, " +
            "SUM(CASE WHEN o.status = 4 THEN 1 ELSE 0 END) as completedCount " +
            "FROM repair_order o " +
            "LEFT JOIN user r ON o.repairman_id = r.id " +
            "WHERE o.repairman_id IS NOT NULL " +
            "GROUP BY r.id, r.username " +
            "ORDER BY order_count DESC")
    List<Map<String, Object>> selectRepairmanStatistics();

    @Select("SELECT status, COUNT(*) as count FROM repair_order GROUP BY status")
    List<Map<String, Object>> selectOrderStatus();

    @Select("SELECT r.real_name as name, " +
            "COUNT(*) as totalOrders, " +
            "SUM(CASE WHEN o.status = 3 THEN 1 ELSE 0 END) as completedOrders, " +
            "ROUND(SUM(CASE WHEN o.status = 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as completionRate " +
            "FROM repair_order o " +
            "JOIN user r ON o.repairman_id = r.id " +
            "GROUP BY r.id, r.real_name")
    List<Map<String, Object>> selectRepairmanStats();

    @Select("SELECT COUNT(*) FROM repair_order WHERE status = 0")
    Long selectPendingOrdersCount();

    @Select("SELECT COUNT(*) FROM repair_order WHERE DATE(create_time) = CURDATE()")
    Long selectTodayOrdersCount();

    @Select("SELECT o.*,r.real_name as repairmanName, u.username as userName, u.phone as userPhone " +
            "FROM repair_order o " +
            "LEFT JOIN user u ON o.user_id = u.id " +
            "LEFT JOIN user r on o.repairman_id = r.id " +
            "WHERE o.deleted = 0 " +
            "AND (#{userId} IS NULL OR o.user_id = #{userId}) " +
            "AND (#{repairmanId} IS NULL OR o.repairman_id = #{repairmanId}) " +
            "ORDER BY o.create_time DESC")
    List<Map<String, Object>> selectOrderList(Long userId, Long repairmanId);

    @Select("SELECT o.*, u.username as userName, u.phone as userPhone " +
            "FROM repair_order o " +
            "LEFT JOIN user u ON o.user_id = u.id " +
            "WHERE o.id = #{orderId} AND o.deleted = 0")
    Map<String, Object> selectOrderDetails(Long orderId);

    @Select("SELECT DATE(create_time) as date, COUNT(*) as count " +
            "FROM repair_order " +
            "WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) " +
            "GROUP BY DATE(create_time) " +
            "ORDER BY date")
    List<Map<String, Object>> selectOrderTrend();

    @Select("SELECT r.real_name as name, " +
            "COUNT(CASE WHEN o.status = 3 THEN 1 END) as completed_orders " +
            "FROM repair_order o " +
            "JOIN user r ON o.repairman_id = r.id " +
            "GROUP BY r.id, r.real_name " +
            "ORDER BY completed_orders DESC")
    List<Map<String, Object>> selectRepairmanPerformance();

    @Select("SELECT o.*, u.username as userName, u.phone as userPhone " +
            "FROM repair_order o " +
            "LEFT JOIN user u ON o.user_id = u.id " +
            "WHERE o.status = 0 AND o.deleted = 0 " +
            "ORDER BY o.create_time DESC")
    List<Map<String, Object>> selectPendingOrders();

    @Select("SELECT o.*, u.username as userName, u.phone as userPhone " +
            "FROM repair_order o " +
            "LEFT JOIN user u ON o.user_id = u.id " +
            "WHERE o.repairman_id = #{repairmanId} " +
            "AND o.status = 3 " +
            "AND o.deleted = 0 " +
            "ORDER BY o.create_time DESC")
    List<Map<String, Object>> selectCompletedOrders(Long repairmanId);

    /**
     * 查询维修工在指定时间范围内完成的订单
     */
    @Select("SELECT * FROM repair_order " +
            "WHERE repairman_id = #{repairmanId} " +
            "AND status = 3 " + // 3表示已完成
            "AND create_time >= #{startTime} " +
            "AND create_time < #{endTime} " +
            "ORDER BY create_time ASC")
    List<RepairOrder> findCompletedOrdersByRepairmanAndDate(
        @Param("repairmanId") Long repairmanId,
        @Param("startTime") LocalDateTime startTime,
        @Param("endTime") LocalDateTime endTime
    );

    @Select("SELECT o.*, u.username as userName, u.phone as userPhone " +
            "FROM repair_order o " +
            "LEFT JOIN user u ON o.user_id = u.id " +
            "WHERE o.repairman_id = #{id} " +
            "AND o.status = 1 " +
            "AND o.deleted = 0 " +
            "ORDER BY o.create_time DESC")
    List<Map<String, Object>> selectAcceptedOrders(Long id);

    @Select("SELECT o.*, u.username as userName, u.phone as userPhone " +
            "FROM repair_order o " +
            "LEFT JOIN user u ON o.user_id = u.id " +
            "WHERE o.repairman_id = #{id} " +
            "AND o.status = 2 " +
            "AND o.deleted = 0 " +
            "ORDER BY o.create_time DESC")
    List<Map<String, Object>> selectRepairingOrders(Long id);

    @Select("SELECT o.*, u.username as userName, u.phone as userPhone " +
            "FROM repair_order o " +
            "LEFT JOIN user u ON o.user_id = u.id " +
            "WHERE o.repairman_id = #{id} " +
            "AND o.status = 4 " +
            "AND o.deleted = 0 " +
            "ORDER BY o.create_time DESC")
    List<Map<String, Object>> selectCancelledOrders(Long id);
}